package br.com.pucrs.tcc.bd;

import java.util.ArrayList;

import org.hibernate.SQLQuery;
import org.hibernate.Session;

import br.com.pucrs.tcc.ed.Convite;

public class ConviteBD {

	private Session mSession;

	public ConviteBD(Session session) {
		mSession = session;
	}

	public Convite insert(Convite convite) {
		mSession.save(convite);
		mSession.flush();

		return convite;
	}

	public void update(Convite convite) {
		mSession.update(convite);
	}

	public void delete(Convite convite) {
		mSession.delete(convite);
		mSession.flush();
	}

	public Convite retrieve(Convite convite) {
		Convite conviteRetorno = null;

		conviteRetorno = (Convite) mSession.load(Convite.class, convite.getId());

		return conviteRetorno;
	}

	public ArrayList<Convite> listSent(String matriculaAluno) {
		ArrayList<Convite> listaRetorno = null;

		StringBuilder sql = new StringBuilder();

		sql.append(" SELECT con.* ");
		sql.append("   FROM convites con ");
		sql.append("  WHERE con.alunos_matricula =  ? ");

		SQLQuery query = mSession.createSQLQuery(sql.toString());
		query.addEntity(Convite.class);
		query.setString(0, matriculaAluno);

		listaRetorno = (ArrayList<Convite>) query.list();

		return listaRetorno;
	}

	public ArrayList<Convite> listReceived(String matriculaAluno) {
		ArrayList<Convite> listaRetorno = null;

		StringBuilder sql = new StringBuilder();

		sql.append(" SELECT con.* ");
		sql.append("   FROM convites con ");
		sql.append("  WHERE con.aluno_convidado =  ? ");

		SQLQuery query = mSession.createSQLQuery(sql.toString());
		query.addEntity(Convite.class);
		query.setString(0, matriculaAluno);

		listaRetorno = (ArrayList<Convite>) query.list();

		return listaRetorno;
	}

	public Integer countContivePendente(Convite convite) {
		Integer count = 0;

		StringBuilder sql = new StringBuilder();

		sql.append(" SELECT c.* ");
		sql.append("   FROM convites c ");
		sql.append("  WHERE c.aluno_convidado = ? ");
		sql.append("    AND c.grupos_trabalho_id = ? ");
		sql.append("    AND c.situacao = 1 ");

		SQLQuery query = mSession.createSQLQuery(sql.toString());
		query.addEntity(Convite.class);
		query.setString(0, convite.getConvidado().getMatricula());
		query.setInteger(1, convite.getGrupoTrabalho().getId());

		count = query.list().size();

		return count;
	}
	
	
	public ArrayList<Convite> listaAceitos(String matriculaAluno) {
		ArrayList<Convite> listaRetorno = null;

		StringBuilder sql = new StringBuilder();

		sql.append(" SELECT con.* ");
		sql.append("   FROM convites con ");
		sql.append("  WHERE con.aluno_convidado =  ? ");
		sql.append("  	AND con.situacao =  3 ");

		SQLQuery query = mSession.createSQLQuery(sql.toString());
		query.addEntity(Convite.class);
		query.setString(0, matriculaAluno);

		listaRetorno = (ArrayList<Convite>) query.list();

		return listaRetorno;
	}

}
